﻿using System;
using System.Configuration;
using System.Collections.Specialized;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Management.Smo;



/* 1. create a copy of the XML file with special characters removed
 * 2. log all corrections to "RemoveSpecialCharactersLog.txt"
 * 3. read the corrected XML file into a dataset
 * 4. for each dataTable in the dataSet, create a corresponding table in SQL inferring the SQL datatype from each dataset column
 * 5. use SqlBulkCopy to write the data from the Dataset table into SQL
 * Phil Cohen 2014
 */
namespace ReadEnum
{
    class Program
    {
        static void Main(string[] args)
        {
            
            DataSet ds = new DataSet("EnumData");


            if (args.Count() != 2)
            {
                Console.WriteLine("Xml2Sql.exe sourceXmlFile.XML cleanedXmlFile.XML");
                return;
            }

            //args[0] is the source XML document, args[1] is the target with special characters removed
            try
            {
                CleanTheXML(args[0], args[1]);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error processing the source XML document");
                Console.WriteLine(e);
                return;
            }
            Server srv;
            string dbName;
            string connectionString;
            int bulkCopyTimeout;
            try
            {
                // get the connection string from the app.config
                ConnectionStringSettings cs = ConfigurationManager.ConnectionStrings["connString"];

                srv = new Server(cs.ProviderName);
                connectionString = cs.ConnectionString;

                // get the db name (aka "Initial Catalog")
                int i = connectionString.IndexOf("Initial Catalog=") + 16;

                string temp = connectionString.Substring(i);

                i = temp.IndexOf(";");
                dbName = temp.Substring(0, i);

                // get the bulk copy timeout setting
                temp = ConfigurationManager.AppSettings["bulkCopyTimeout"];
                bulkCopyTimeout = Convert.ToInt32(temp);
            }
            catch (Exception e)
            {
                Console.WriteLine("error reading from config file");
                Console.WriteLine(e.Message);
                return;
            }

            // create the database
            Database db = new Database(srv, dbName);
            try
            {

                db.Create();

            }
            catch (Exception e)
            {
                Console.WriteLine("Error creating database:");
                Console.WriteLine(e);
                return;
            }

            // read the cleaned XML document into a dataset
            ds.ReadXml(args[1]);


            foreach (DataTable table in ds.Tables)
            {
                Console.WriteLine("TableName: " + table.TableName);
                // create the table in the database
                Table tb = new Table(db, table.TableName);

                foreach (DataColumn column in table.Columns)
                {

                    // add the columns to the database
                    Column col = new Column(tb, column.ToString(), LookupSqlDataType(column.DataType.ToString()));

                    tb.Columns.Add(col); 
                }
                tb.Create();
                //Console.WriteLine();
                // save the data for this table
                using (SqlConnection destinationConnection =
                       new SqlConnection(connectionString))
                {

                    destinationConnection.Open();

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
                    {
                        bulkCopy.DestinationTableName = "dbo.[" + table.TableName + "]";

                        try
                        {
                            //change the default timeout period from 30 sec - needed for large table copies
                            bulkCopy.BulkCopyTimeout = bulkCopyTimeout;
                            // Write from the source to the destination.
                            bulkCopy.WriteToServer((DataTable)table);
                            
                            Console.WriteLine("--successfully wrote table: " + table.TableName);
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }
                    }

                }
            }
            Console.WriteLine();

        }
        static DataType LookupSqlDataType(String dtSource)
        {
            switch (dtSource)
            {
                case "System.Int32":
                    return DataType.Int;
                    
                case "System.String":
                    return DataType.NVarCharMax;
                    
                default:
                    Console.WriteLine("Unknown type");
                    throw new FormatException("Unhandled source datatype");
                    
            }
            
        }
        static void CleanTheXML(string sourceXmlFile, string targetXmlFile)
        {
            try
            {
                // delete the old log file if it exists
                try
                {
                    File.Delete("RemoveSpecialCharactersLog.txt");
                }
                catch(Exception e)
                {
                    Console.WriteLine(e.Message);
                }
                // Create an instance of StreamReader to read from a file.
                // The using statement also closes the StreamReader.
                using (StreamReader sr = new StreamReader(sourceXmlFile))
                {
                    using (StreamWriter sw = new StreamWriter(targetXmlFile))
                    {
                        String sIn, sOut;

                        while ((sIn = sr.ReadLine()) != null)
                        {
                            sOut = RemoveSpecialCharacters(sIn);
                            sw.WriteLine(sOut);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                // Let the user know what went wrong.
                Console.WriteLine("The file could not be read:");
                Console.WriteLine(e.Message);
            }
        }
        public static string RemoveSpecialCharacters(string str)
        {
            bool logEntry = false;
            string doubleQuote;

            StringBuilder sb = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();

            foreach (char c in str)
            {
                if (c >= ' ' && c <= '~')
                {
                    sb.Append(c);
                }
                else
                {
                    logEntry = true;
                    sb.Append("X");
                }
            }
            if (logEntry == true)
            {
                LogAString(str);
                logEntry = false;
            }
            //check for embedded " in the URL of Web ID records
            doubleQuote = sb.ToString();

            // Is it a Web ID record?
            if (doubleQuote.Contains("<Web Id="))
            {
                // ensure the string contains URL and LanguageId tags
                if (doubleQuote.Contains("Url=") && doubleQuote.Contains("LanguageId="))
                {
                    int iStart, iEnd, i = 0;

                    // look for double quotes between the Url=" and " LanguageId=
                    // i.e. embedded double quotes
                    iStart = doubleQuote.IndexOf("Url=") + 5;
                    iEnd = doubleQuote.IndexOf("LanguageId=") - 3;

                    foreach (char c in doubleQuote)
                    {
                        if ((i < iStart) || (i > iEnd))
                        {
                            sb2.Append(c);
                        }
                        else if (c != '"')
                        {
                            sb2.Append(c);
                        }
                        else
                        {
                            logEntry = true;
                        }
                        i++;
                    }
                    if (logEntry == true)
                    {
                        doubleQuote = sb2.ToString();
                        LogAString(str);
                        logEntry = false;
                    }
                }
            }


            return doubleQuote;
        }
        public static void LogAString(string str)
        {
            try
            {
                using (StreamWriter w = File.AppendText("RemoveSpecialCharactersLog.txt"))
                {
                    w.WriteLine(str);
                    // Close the writer and underlying file.
                    w.Close();
                }
            }
            catch (Exception e)
            {
                // Let the user know what went wrong.
                Console.WriteLine("Error logging:");
                Console.WriteLine(e.Message);
            }
        }
    }
}
